if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsDNSeedNewAssetProtocols]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsDNSeedNewAssetProtocols]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			spFdsDNSeedNewAssetProtocols
* Purpose:		Using table FdsProtocolStagingHumanProtocols, seed core Protocols tables
*			with IDs for all new protocols. The seeding consists of seeding the SvcEntityIds, 
*			FwkResourceIds, AnnotationContainerIds and RevReviewContainerIds.
*			Additionally, once the core protocol data is populated, from the staging tables, 
*			the other related tables for organization, sponsors are populated. Additionally, 
*			access is granted to the protocol as well as the protocol's PI.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/

CREATE PROCEDURE dbo.spFdsDNSeedNewAssetProtocols
AS
BEGIN


BEGIN TRANSACTION spFdsDNSeedNewAssetProtocols

	------------------------------------------------------------------------------------
	-- * Prepare the various temporary tables to use for the seeding process. 
	------------------------------------------------------------------------------------

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededProtocolIDs')
		DROP TABLE tmpSeededProtocolsIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededSvcIDs')
		DROP TABLE tmpSeededSvcIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededFwkResourceIDs')
		DROP TABLE tmpSeededFwkResourceIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAnnotationContainerIDs')
		DROP TABLE tmpSeededAnnotationContainerIDs

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededReviewContainerIDs')
		DROP TABLE tmpSeededReviewContainerIDs

	SET NOCOUNT ON

	DECLARE	@_protocolSeed		int
	DECLARE	@_entitySeed		int
	DECLARE	@_resourceSeed		int
	DECLARE	@_reviewSeed		int
	DECLARE	@_annotationSeed	int
	DECLARE @_fwkResourceTypeId     int
	DECLARE @_svcProtocolEntityTypeId int
	DECLARE @_annProtocolEntityTypeId  int
	DECLARE @_revProtocolEntityTypeId  int
	DECLARE @_principalInvestigatorStaffTypeId int
	DECLARE	@_sql	varchar(1000)
	DECLARE @_subsprocStatus	AS BIT
	DECLARE	@SUCCESS	 Integer
	DECLARE	@FAILURE	 Integer
	DECLARE @_NOTUPDATE AS BIT
	DECLARE @PROTOCOL_LOCKED  AS INTEGER	
	DECLARE @HUM_PROTOCOL_TYPE_ID AS INTEGER
	DECLARE @IS_INBOUND AS BIT

	SET	@SUCCESS = 1
	SET	@FAILURE = 0
	SET @_NOTUPDATE = 0
	set @_fwkResourceTypeId = 1
	set @_svcProtocolEntityTypeId = 2
	set @_annProtocolEntityTypeId = 24
	set @_revProtocolEntityTypeId = 24
        set @_principalInvestigatorStaffTypeId = 1
	SET @PROTOCOL_LOCKED			= 1
	SET @HUM_PROTOCOL_TYPE_ID  = 1
	SET @IS_INBOUND = 1
	
	
	----------------------------------------------------------------------
	
	-- Seed FwkResource data
	
		
	IF (SELECT  ISNULL(IDENT_CURRENT('FwkResource') , 0)) = 0
		SET	@_resourceSeed		= 1
	ELSE
		SET	@_resourceSeed		= IDENT_CURRENT('FwkResource') + 1

	-- FwkResource
	SET IDENTITY_INSERT FwkResource OFF

	INSERT	into FwkResource (FwkResourceTypeId)
	SELECT	@_fwkResourceTypeId
	FROM	FdsProtocolStagingHumanProtocols
   	WHERE IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
		
	-- FwkResourceId
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_resourceSeed AS varchar) + ', 1) AS FwkId, rexProtocolId
                         INTO tmpSeededFwkResourceIDs FROM FdsProtocolStagingHumanProtocols
			 WHERE IsUpdate = ' + CAST(@_NOTUPDATE AS varchar) + '	AND IsInbound  = ' + CAST(@IS_INBOUND AS varchar) 
	EXEC(@_sql)

	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	UPDATE	FdsProtocolStagingHumanProtocols
	SET		FwkResourceId = tmpSeededFwkResourceIDs.FwkId
	FROM	tmpSeededFwkResourceIDs
	WHERE	FdsProtocolStagingHumanProtocols.rexProtocolID = tmpSeededFwkResourceIDs.rexProtocolId
         AND FdsProtocolStagingHumanProtocols.IsUpdate = @_NOTUPDATE
	 AND FdsProtocolStagingHumanProtocols.IsInbound  = @IS_INBOUND 	

	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	DROP TABLE tmpSeededFwkResourceIDs

        -----------------------------------------------------------------------

	-- Seed SvcEntity data
	
	IF (SELECT ISNULL(IDENT_CURRENT('SvcEntity') , 0)) = 0
		SET	@_entitySeed		= 1
	ELSE
		SET	@_entitySeed		= IDENT_CURRENT('SvcEntity') + 1
	
	
	-- SvcEntity
	SET IDENTITY_INSERT SvcEntity OFF

	INSERT	SvcEntity (SvcEntityTypeId, FwkResourceId)
	SELECT	@_svcProtocolEntityTypeId , FwkResourceId
	FROM	FdsProtocolStagingHumanProtocols
	WHERE IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 

	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	-- SvcEntityId
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_entitySeed AS varchar) + ', 1) AS SvcId, rexProtocolId
			 INTO tmpSeededSvcIDs FROM FdsProtocolStagingHumanProtocols
			 WHERE IsUpdate = ' + CAST(@_NOTUPDATE AS varchar) + '	AND IsInbound  = ' + CAST(@IS_INBOUND AS varchar) 
	EXEC(@_sql)
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	UPDATE	FdsProtocolStagingHumanProtocols
	SET		SvcEntityId = tmpSeededSvcIDs.SvcId
	FROM	tmpSeededSvcIDs
	WHERE	FdsProtocolStagingHumanProtocols.rexProtocolID  = tmpSeededSvcIDs.rexProtocolId
	AND  FdsProtocolStagingHumanProtocols.IsUpdate = @_NOTUPDATE
	AND FdsProtocolStagingHumanProtocols.IsInbound  = @IS_INBOUND
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	DROP TABLE tmpSeededSvcIDs

        --------------------------------------------------------------------------

	-- Seed RevReviewContainer Ids
	
	IF (SELECT ISNULL(IDENT_CURRENT('RevReviewContainer') , 0)) = 0
		SET	@_reviewSeed		= 1
	ELSE
		SET	@_reviewSeed		= IDENT_CURRENT('RevReviewContainer') + 1

	
	SET IDENTITY_INSERT RevReviewContainer OFF

	INSERT	RevReviewContainer ( SbsEntityTypeId)
	SELECT	@_revProtocolEntityTypeId 
	FROM	FdsProtocolStagingHumanProtocols
	WHERE IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 
	
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_reviewSeed AS varchar) + ', 1) AS ReviewId, rexProtocolId
			 INTO tmpSeededReviewContainerIDs FROM FdsProtocolStagingHumanProtocols
			 WHERE IsUpdate = ' + CAST(@_NOTUPDATE AS varchar) + '	AND IsInbound  = ' + CAST(@IS_INBOUND AS varchar) 
	EXEC(@_sql)

	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	UPDATE	FdsProtocolStagingHumanProtocols
	SET		RevReviewContainerId = tmpSeededReviewContainerIDs.ReviewId
	FROM	tmpSeededReviewContainerIDs
	WHERE	FdsProtocolStagingHumanProtocols.rexProtocolID = tmpSeededReviewContainerIDs.rexProtocolId
	AND  FdsProtocolStagingHumanProtocols.IsUpdate = @_NOTUPDATE
	AND FdsProtocolStagingHumanProtocols.IsInbound  = @IS_INBOUND	

	IF @@ERROR > 0 GOTO ERROR_HANDLER
		
	DROP TABLE tmpSeededReviewContainerIDs

	--------------------------------------------------------------------------
	
	-- Seed AnnotationContainer Ids

	IF (SELECT ISNULL(IDENT_CURRENT('AnnAnnotationContainer') , 0)) = 0
		SET	@_annotationSeed	= 1
	ELSE
		SET	@_annotationSeed	= IDENT_CURRENT('AnnAnnotationContainer') + 1

	
	-- AnnAnnotationContainer
	SET IDENTITY_INSERT AnnAnnotationContainer OFF

	INSERT	AnnAnnotationContainer ( SbsEntityTypeId)
	SELECT	@_annProtocolEntityTypeId 
	FROM	FdsProtocolStagingHumanProtocols
	WHERE IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	-- AnnAnnotationContainerId
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_annotationSeed AS varchar) + ', 1) AS AnnId, rexProtocolId
			 INTO tmpSeededAnnotationContainerIDs FROM FdsProtocolStagingHumanProtocols
			 WHERE IsUpdate = ' + CAST(@_NOTUPDATE AS varchar) + '	AND IsInbound  = ' + CAST(@IS_INBOUND AS varchar) 
	EXEC(@_sql)
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	UPDATE	FdsProtocolStagingHumanProtocols
	SET		AnnAnnotationContainerId = tmpSeededAnnotationContainerIDs.AnnId
	FROM	tmpSeededAnnotationContainerIDs 
	WHERE	FdsProtocolStagingHumanProtocols.rexProtocolID  = tmpSeededAnnotationContainerIDs.rexProtocolId
	AND  FdsProtocolStagingHumanProtocols.IsUpdate = @_NOTUPDATE
	AND FdsProtocolStagingHumanProtocols.IsInbound  = @IS_INBOUND

	IF @@ERROR > 0 GOTO ERROR_HANDLER
	
	DROP TABLE tmpSeededAnnotationContainerIDs 


	----------------------------------------------------------------------------

	
	IF (SELECT ISNULL(IDENT_CURRENT('PrtProtocol') , 0)) = 0
		SET	@_protocolSeed		= 1
	ELSE
		SET	@_protocolSeed		= IDENT_CURRENT('PrtProtocol') + 1


	-- AgrAgreementId
	SET	@_sql = 'SELECT	IDENTITY(int, ' + CAST(@_protocolSeed AS varchar) + ', 1) AS protocolId, rexProtocolId
			 INTO tmpSeededProtocolsIDs FROM FdsProtocolStagingHumanProtocols
			 WHERE IsUpdate = ' + CAST(@_NOTUPDATE AS varchar) + '	AND IsInbound  = ' + CAST(@IS_INBOUND AS varchar) 
	EXEC(@_sql)
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	UPDATE	FdsProtocolStagingHumanProtocols
	SET		ProtocolId = tmpSeededProtocolsIDs.protocolId
	FROM	tmpSeededProtocolsIDs 
	WHERE	FdsProtocolStagingHumanProtocols.rexProtocolID   = tmpSeededProtocolsIDs.RexProtocolId
	AND  FdsProtocolStagingHumanProtocols.IsUpdate = @_NOTUPDATE
	AND FdsProtocolStagingHumanProtocols.IsInbound  = @IS_INBOUND
	
	IF @@ERROR > 0 GOTO ERROR_HANDLER	

	DROP TABLE tmpSeededProtocolsIDs 


	-- PrtProtocol 
	SET IDENTITY_INSERT PrtProtocol ON

	INSERT  PrtProtocol (
		Id,
		Title, 
		ShortTitle,
		LastModified,
		ApprovalDate, 
		ExpirationDate,
		SubmissionDate,
		CreationDate,
		ProtocolNumber,
		PrpProposalId,
		FwkResourceId,
		PrtProtocolStatusTypeId,
		PrtProtocolSubStatus,
		PrtProtocolWorkflowStatus,
		LastModifierDmnUserId,
		AnnAnnotationContainerId,
		PrtProtocolTemplateId,
		RevReviewContainerId,
		IsLocked,
		SvcEntityId,
		PrtProtocolTypeId,
		LastModifierAdmPersonId
		)
	select	ProtocolId,
	Title, 
	ShortTitle, 
	LastModified,
	ApprovalDate, 
	ExpirationDate,
	SubmissionDate,
	CreationDate,
	ProtocolNumber,
	PrpProposalId,
	FwkResourceId,
	PrtProtocolStatusTypeId,
	PrtProtocolSubStatus,
	PrtProtocolWorkflowStatus,
	LastModifierDmnUserId,
	AnnAnnotationContainerId,
	PrtProtocolTemplateId,
	RevReviewContainerId,
	@PROTOCOL_LOCKED,
	SvcEntityId,
	@HUM_PROTOCOL_TYPE_ID ,
	LastModifierAdmPersonId
  FROM FdsProtocolStagingHumanProtocols
  WHERE IsUpdate = @_NOTUPDATE
  AND IsInbound  = @IS_INBOUND 

  
	IF @@ERROR > 0 GOTO ERROR_HANDLER	

---- HumHumanProtocol Population

--- Insert into HumHumanProtocol
	
    Insert into HumHumanProtocol(PrtProtocolId)
    Select protocolId from FdsProtocolStagingHumanProtocols
    WHERE IsUpdate = @_NOTUPDATE
    AND IsInbound  = @IS_INBOUND 
	
    
	IF @@ERROR > 0 GOTO ERROR_HANDLER


--- Insert the PI staffing  Information for this protocols
 
    Insert SvcEntity_AdmStaffedPerson_Map (  SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId, DateCreated ) 
    SELECT SvcEntityId, InitialOwnerAdmPersonId, @_principalInvestigatorStaffTypeId, getDate()
    from FdsProtocolStagingHumanProtocols
	WHERE Isupdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 
	

     IF @@ERROR > 0 GOTO ERROR_HANDLER
	
--- Insert the PrtStaffedPerson information
 	
    INSERT INTO PrtStaffedPerson(SvcEntityStaffedPersonMapId, HasCertified, FwkOrganizationId)
	SELECT sam.Id, 0, coalesce(psp.PrtFwkOrgId, psp.InitialOwnerFwkPrimaryOrgId, NULL)
	FROM SvcEntity_AdmStaffedPerson_Map sam
	INNER JOIN FdsProtocolStagingHumanProtocols psp
	ON sam.svcEntityId = psp.SvcEntityId
	AND sam.AdmPersonId = psp.InitialOwnerAdmPersonId
	WHERE psp.IsUpdate = @_NOTUPDATE
	AND psp.IsInbound  = @IS_INBOUND 	    
	AND sam.AdmStaffedPersonTypeId = @_principalInvestigatorStaffTypeId
	
   
	IF @@ERROR > 0 GOTO ERROR_HANDLER


--- Insert the Sponsor information for those sponsors that were successfully mapped 
   
    Insert into SvcEntity_AdmSponsor_map ( SvcEntityId, AdmSponsorId, 
					AdmSponsorRelationTypeId, DateCreated)
    SELECT SvcEntityId, AdmSponsorId, 1 as AdmSponsorRelationTypeId,
      			 getDate() as DateCreated
    FROM FdsProtocolStagingHumanProtocols
    WHERE IsUpdate = @_NOTUPDATE AND IsInbound  = @IS_INBOUND  AND AdmSponsorId is not null

	IF @@ERROR > 0 GOTO ERROR_HANDLER
    	
	
---Insert the Organization information for organizations that were 
---successfully mapped. This uses either the FQDN map or the institution
---and Chief code (Unit chief code if one exists or department code)

   Insert into SvcEntity_FwkDomainOrganization_Map ( SvcEntityId, FwkDomainOrganizationid,
		 FwkOrganizationRelationshipTypeId, DateCreated)
   SELECT SvcEntityId, coalesce(PrtFwkOrgId, InitialOwnerFwkPrimaryOrgId, 0),
		 1 as FwkOrganizationRelationshipTypeId, getDate()
   FROM FdsProtocolStagingHumanProtocols
   WHERE IsUpdate = @_NOTUPDATE AND IsInbound  = @IS_INBOUND 
	AND not ( PrtFwkOrgId is null and InitialOwnerFwkPrimaryOrgId is null)

   IF @@ERROR > 0 GOTO ERROR_HANDLER	

--- grant access for PI as an owner and  manager for the new protocols.
  	
	
	
	DECLARE @_READ_ACCESS_BITMASK AS INTEGER
	DECLARE @_MANAGE_ACCESS_BITMASK AS INTEGER
	DECLARE @_EXPLICIT_PROVISION_BITVALUE AS INTEGER
	DECLARE @_OWNER_PROVISION_BITVALUE AS INTEGER
		
	SET @_EXPLICIT_PROVISION_BITVALUE = 2
	SET @_OWNER_PROVISION_BITVALUE = 8

	SET @_READ_ACCESS_BITMASK = (SELECT dbo.fnGetAccessRightBitvalueByName ('Read'))
	SET @_MANAGE_ACCESS_BITMASK = (@_READ_ACCESS_BITMASK +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Manage')) +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Edit')) +
					(SELECT dbo.fnGetAccessRightBitvalueByName('Delete')))
	

	DECLARE curPIAccessRights CURSOR LOCAL FAST_FORWARD FOR
	
	SELECT InitialOwnerFwkPrincipalId, FwkResourceId
    	FROM FdsProtocolStagingHumanProtocols
	WHERE InitialOwnerFwkPrincipalId is not null 
	AND IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 

	DECLARE @_piResourceId AS INTEGER
	DECLARE @_principalId AS INTEGER

	OPEN curPIAccessRights 
	FETCH NEXT FROM curPIAccessRights INTO @_principalId ,  @_piResourceId

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXEC @_subsprocStatus = spFwkNGrantAccess  @_principalId ,  @_piResourceId,
					  @_OWNER_PROVISION_BITVALUE, @_MANAGE_ACCESS_BITMASK

		
		IF @_subsprocStatus = @FAILURE GOTO ERROR_HANDLER

		FETCH NEXT FROM curPIAccessRights INTO @_principalId,  @_piResourceId
		
	END

	
	-- close and deallocate cursor
	CLOSE curPIAccessRights
	DEALLOCATE curPIAccessRights 

--- Grant access to the new protocols based on the PrtAccessControlRules table

    DECLARE curResources CURSOR LOCAL FAST_FORWARD FOR
	select FwkResourceId from FdsProtocolStagingHumanProtocols
	WHERE IsUpdate = @_NOTUPDATE
	AND IsInbound  = @IS_INBOUND 


	DECLARE @_resourceId AS INTEGER

	OPEN curResources
	FETCH NEXT FROM curResources INTO @_resourceId

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXEC @_subsprocStatus = spPrtNGrantAccessByControlRules @_resourceId
		
		IF @_subsprocStatus = @FAILURE GOTO ERROR_HANDLER

		FETCH NEXT FROM curResources INTO @_resourceId
		
	END

	-- close and deallocate cursor
	CLOSE curResources
	DEALLOCATE curResources 


---- Check for errors and roll back 
	
 IF @@ERROR <> 0
 BEGIN
	GOTO ERROR_HANDLER
 END
			
SET NOCOUNT OFF
COMMIT TRANSACTION  spFdsDNSeedNewAssetProtocols
RETURN @SUCCESS

ERROR_HANDLER: 
  SET NOCOUNT OFF
  ROLLBACK TRANSACTION spFdsDNSeedNewAssetProtocols
 RETURN @FAILURE



END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

